﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace EPlatform_web.App_Start
{
    public class OracleOperation
    {
        private const string ssContract = "EMC";
        private const string ssDbName = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.3)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=emtest)))";
        private const string ssConnUid = "ifsapp";
        private const string ssConnPwd = "ifs#mis";
        /// <summary>
        /// 根據SQL語句查詢
        /// </summary>
        /// <param name="sql">SQL語句</param>
        /// <returns>DataSet</returns>
        public static DataSet QueryData(string sql, string sContract = ssContract, string sDbName = ssDbName, string sConnUid = ssConnUid, string sConnPwd = ssConnPwd)
        {
            DbObject dbObject = new DbObject( sDbName, sConnUid, sConnPwd);
            string sqlQuery = sql;
            DataSet dataSet = dbObject.GetDataSet(sqlQuery);
            return dataSet;
        }
        /// <summary>
        /// 執行SQL語句
        /// </summary>
        /// <param name="sql">SQL語句</param>
        /// <returns>受影響的行數</returns>
        public static int Exec(string sql, string sContract = ssContract, string sDbName = ssDbName, string sConnUid = ssConnUid, string sConnPwd = ssConnPwd)
        {
            DbObject dbObject = new DbObject( sDbName, sConnUid, sConnPwd);
            string sqlQuery = sql;
            int ds = dbObject.RunSql(sqlQuery);
            return ds;
        }
        /// <summary>
        /// 執行存儲過程（帶輸出參數）
        /// </summary>
        /// <param name="procedureName">存儲過程名稱</param>
        /// <param name="parmeterdata">存儲過程參數</param>
        /// <returns>輸出參數</returns>
        public static string ExecProcedureOut(string procedureName, IDataParameter[] parmeterdata, string sContract = ssContract, string sDbName = ssDbName, string sConnUid = ssConnUid, string sConnPwd = ssConnPwd)
        {
            DbObject dbObject = new DbObject( sDbName, sConnUid, sConnPwd);
            string docNo = dbObject.RunProcedureout(procedureName, parmeterdata);
            return docNo;
        }
        /// <summary>
        /// 執行存儲過程
        /// </summary>
        /// <param name="procedureName">存儲過程名稱</param>
        /// <param name="parmeterdata">存儲過程參數</param>
        public static void ExecProcedure(string procedureName, IDataParameter[] parmeterdata, string sContract = ssContract, string sDbName = ssDbName, string sConnUid = ssConnUid, string sConnPwd = ssConnPwd)
        {
            DbObject dbObject = new DbObject( sDbName, sConnUid, sConnPwd);
            dbObject.RunProcedure(procedureName, parmeterdata);
        }
        /// <summary>
        /// 根據SQL語句查詢值
        /// </summary>
        /// <param name="sql">SQL語句</param>
        /// <returns>第一行第一列值</returns>
        public static string GetSelectValue(string sql, string sContract = ssContract, string sDbName = ssDbName, string sConnUid = ssConnUid, string sConnPwd = ssConnPwd)
        {
            DbObject dbObject = new DbObject( sDbName, sConnUid, sConnPwd);
            string sqlQuery = sql;
            DataSet dataSet = dbObject.GetDataSet(sqlQuery);
            string result = "";
            if (dataSet.Tables.Count > 0)
            {
                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    result = dataSet.Tables[0].Rows[0][0].ToString();
                }
            }
            return result;
        }
    }
}